package com.parking.dao.impl;

import com.parking.bean.ParkSpace;
import com.parking.dao.ParkSpaceDao;
import com.parking.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ParkSpaceDaoImpl implements ParkSpaceDao {
    @Override
    public List<ParkSpace> list() {
        /*链接数据库*/
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet=null;
        List<ParkSpace>list = new ArrayList<>();
        try {
            /*获取链接*/
            conn = DBUtil.getConnection();
            /*获取预编译的数据库操作对象*/
            String sql = "select * from park_space ";
            ps = conn.prepareStatement(sql);
            /*执行Sql语句*/
            resultSet = ps.executeQuery();
            /*处理结果集*/
            while (resultSet.next()) {
                Integer id =resultSet.getInt(1);
                String parkSpaceId =resultSet.getString(2);
                String local =resultSet.getString(3);
                String parkSpaceState=resultSet.getString(4);
                String parkSpaceCreateDate =resultSet.getString(5);
                list.add(new ParkSpace(id,parkSpaceId,local,parkSpaceState,parkSpaceCreateDate));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                /*释放资源*/
                DBUtil.close(conn, ps, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<ParkSpace> search(String key, String value) {
        /*链接数据库*/
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet=null;
        List<ParkSpace>list = new ArrayList<>();
        try {
            /*获取链接*/
            conn = DBUtil.getConnection();
            /*获取预编译的数据库操作对象*/
            /*String sql = "select * from park_admin where ? like '%'?'%'";*/
            String sql = "select * from park_space where "+key+" like '%"+value+"%'";
            ps = conn.prepareStatement(sql);
            /*执行Sql语句*/
            resultSet = ps.executeQuery();
            /*处理结果集*/
            while (resultSet.next()) {
                Integer id =resultSet.getInt(1);
                String parkSpaceId =resultSet.getString(2);
                String local =resultSet.getString(3);
                String parkSpaceState=resultSet.getString(4);
                String parkSpaceCreateDate =resultSet.getString(5);
                list.add(new ParkSpace(id,parkSpaceId,local,parkSpaceState,parkSpaceCreateDate));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                /*释放资源*/
                DBUtil.close(conn, ps, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public Integer save(ParkSpace parkSpace) {
        /*链接数据库*/
        Connection conn = null;
        PreparedStatement ps = null;
        Integer result=null;
        try {
            /*获取链接*/
            conn = DBUtil.getConnection();
            /*获取预编译的数据库操作对象*/
            /*String sql = "select * from park_admin where ? like '%'?'%'";*/
            //parkSpaceState和parkSpaceCreateDate这两个属性不需要用户填写
            String sql = "insert into park_space(parkSpaceId,local) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,parkSpace.getParkSpaceId());
            ps.setString(2,parkSpace.getLocal());
            /*执行Sql语句*/
            result = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                /*释放资源*/
                DBUtil.close(conn, ps, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public Integer update(ParkSpace parkSpace) {
        /*链接数据库*/
        Connection conn = null;
        PreparedStatement ps = null;
        Integer result=null;
        try {
            /*获取链接*/
            conn = DBUtil.getConnection();
            /*获取预编译的数据库操作对象*/
            /*String sql = "select * from park_admin where ? like '%'?'%'";*/
            String sql = "update park_space set parkSpaceId=?,local=?,parkSpaceState=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,parkSpace.getParkSpaceId());
            ps.setString(2,parkSpace.getLocal());
            ps.setString(3,parkSpace.getParkSpaceState());
            ps.setInt(4,parkSpace.getId());
            /*执行Sql语句*/
            result = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                /*释放资源*/
                DBUtil.close(conn, ps, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public Integer delete(Integer id) {
        Connection conn = null;
        PreparedStatement ps = null;
        Integer result=null;
        try {
            /*获取链接*/
            conn = DBUtil.getConnection();
            /*获取预编译的数据库操作对象*/
            /*String sql = "select * from park_admin where ? like '%'?'%'";*/
            String sql = "delete from park_space where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            /*执行Sql语句*/
            result = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                /*释放资源*/
                DBUtil.close(conn, ps, null);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }
}
